package internetware.contextserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;


public class DBAdapter {
	static private String dbname = "internetware";
	static private String dbuser = "iw_user";
	static private String dbpasswd = "wareinternet";
	static private String driver = "com.mysql.jdbc.Driver"; 
	static private String dburl = "jdbc:mysql://127.0.0.1:3306/";
	static public void initDBForServer()
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "delete from activeuser";
			statement.execute(sql);
			sql = "delete from context_table_android";
			statement.execute(sql);
			sql = "delete from context_table_windows_xp";
			statement.execute(sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	static public boolean isActive(String user,String platform)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "select * from activeuser where username='"+user+"' and platform ='"+platform+"'";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next())
			{
				return true;
			}
			else
			{
				return false;
			}
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	static public boolean authUser(String user,String userpwd)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "select * from user where username='"+user+"' and passwd ='"+userpwd+"'";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next())
			{
				return true;
			}
			else
			{
				return false;
			}
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;	
	}
	static public void setActive(String user,String platform)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "insert into activeuser values('"+user+"','"+platform+"')";
			statement.execute(sql);
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	static public void setInActive(String user,String platform)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "delete from activeuser where username ='"+user+"' and platform = '"+platform+"'";
			statement.execute(sql);
			if(platform.equals("windows_xp"))
			{
				sql = "delete from context_table_windows_xp where username='"+user+"'";
				statement.execute(sql);	
			}
			else if(platform.equals("android"))
			{
				sql = "delete from context_table_android where username='"+user+"'";
				statement.execute(sql);	
			}
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	static public void saveContext(String platform,String info,String user,String id)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql;
			if(platform.equals("windows_xp"))
			{
				String[] infos = info.split("--");
				String memory = "";
				String os = "";
				String language = "";
				String screen = "";
				for(int i=0;i<infos.length;i++)
				{
					if(infos[i].contains("memory"))
					{
						memory = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));						
					}
					else if(infos[i].contains("os"))
					{
						os = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("language"))
					{
						language = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("screen"))
					{
						screen = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
				}
				
				sql = "delete from context_table_windows_xp where username='"+user+"'";
				statement.execute(sql);		
				sql = "insert into context_table_windows_xp(username,time,memory,os,language,screen,id) " +
							"values(?,?,?,?,?,?,?)";
				PreparedStatement p = (PreparedStatement) conn.prepareStatement(sql);
				p.setString(1, user);
				Date date = new Date();
				p.setTimestamp(2, new Timestamp(date.getTime())	);
				p.setString(3, memory);
				p.setString(4, os);
				p.setString(5, language);
				p.setString(6, screen);
				p.setString(7, id);
				p.execute();				
			}
			else if(platform.equals("android"))
			{
				String[] infos = info.split("--");
				String memory = "";
				String os = "";
				String language = "";
				String screen = "";
				String location = "";
				for(int i=0;i<infos.length;i++)
				{
					if(infos[i].contains("memory"))
					{
						memory = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));						
					}
					else if(infos[i].contains("os"))
					{
						os = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("language"))
					{
						language = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("screen"))
					{
						screen = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("location"))
					{
						location = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					sql = "delete from context_table_android where username='"+user+"'";
					statement.execute(sql);		
					sql = "insert into context_table_android(username,time,memory,os,language,screen,id,location) " +
								"values(?,?,?,?,?,?,?,?)";
					PreparedStatement p = (PreparedStatement) conn.prepareStatement(sql);
					p.setString(1, user);
					Date date = new Date();
					p.setTimestamp(2, new Timestamp(date.getTime())	);
					p.setString(3, memory);
					p.setString(4, os);
					p.setString(5, language);
					p.setString(6, screen);
					p.setString(7, id);
					p.setString(8, location);
					p.execute();
				}
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		
	}
	static public void saveContextHistory(String platform,String info, String user,int type,String id)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			if(platform.equals("windows_xp"))
			{
				String[] infos = info.split("--");
				String memory = "";
				String os = "";
				String language = "";
				String screen = "";
				for(int i=0;i<infos.length;i++)
				{
					if(infos[i].contains("memory"))
					{
						memory = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));						
					}
					else if(infos[i].contains("os"))
					{
						os = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("language"))
					{
						language = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("screen"))
					{
						screen = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
				}
				String sql = "insert into history_table_windows_xp(username,time,memory,os,language,screen,type,id) " +
				"values(?,?,?,?,?,?,?,?)";
				PreparedStatement p = (PreparedStatement) conn.prepareStatement(sql);
				p.setString(1, user);
				Date date = new Date();
				p.setTimestamp(2, new Timestamp(date.getTime())	);
				p.setString(3, memory);
				p.setString(4, os);
				p.setString(5, language);
				p.setString(6, screen);
				p.setInt(7, type);
				p.setString(8, id);
				p.execute();
			}
			else if(platform.equals("android"))
			{
				String[] infos = info.split("--");
				String memory = "";
				String os = "";
				String language = "";
				String screen = "";
				String location = "";
				for(int i=0;i<infos.length;i++)
				{
					if(infos[i].contains("memory"))
					{
						memory = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));						
					}
					else if(infos[i].contains("os"))
					{
						os = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("language"))
					{
						language = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("screen"))
					{
						screen = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
					else if(infos[i].contains("location"))
					{
						location = infos[i].substring(infos[i].indexOf("{")+1, infos[i].indexOf("}"));
					}
				}
				String sql = "insert into history_table_android(username,time,memory,os,language,screen,location,type,id) " +
				"values(?,?,?,?,?,?,?,?,?)";
				PreparedStatement p = (PreparedStatement) conn.prepareStatement(sql);
				p.setString(1, user);
				Date date = new Date();
				p.setTimestamp(2, new Timestamp(date.getTime())	);
				p.setString(3, memory);
				p.setString(4, os);
				p.setString(5, language);
				p.setString(6, screen);
				p.setString(7, location);
				p.setInt(8, type);
				p.setString(9, id);
				p.execute();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	static public String getPlatform(String deviceid,String username)
	{
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(dburl+dbname, dbuser, dbpasswd);
			Statement statement = (Statement) conn.createStatement();
			String sql = "select * from device where username ='"+username+"' and id = '"+deviceid+"'";
			ResultSet r= statement.executeQuery(sql);
			if(r.next())
			{
				return r.getString("platform");
			}
			else
			{
				return "error";
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return "";
	}
	
}
